package com.guodi.common.utils;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.util.CollectionUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
* @描述: 统一Excel操作工具类
* @作者: 代鹏刚
* @日期: 2022/4/26 15:05
**/
@Slf4j
public class ExcelUtil {

    /**
     * Excel ;2003
     */
    public static final String EXCEL_XLS = "xls";

    /**
     * Excel 2007/2010
     */
    public static final String EXCEL_XLSX = "xlsx";

    /**
     * 导出分页最大值
     */
    private static final Integer BIG_EXCEL_SIZE = 10000;

    /**
     * @描述: 从文件中获取Workbook对象
     * @入参:
     * @出参:
     * @作者: 余家鑫
     * @日期: 2021/4/14 上午10:15
     */
    public static Workbook getWorkbook(MultipartFile excel) throws IOException {
        String fileName = excel.getOriginalFilename();
        InputStream in = excel.getInputStream();
        Workbook wb = null;
        if (fileName.endsWith(EXCEL_XLS)) {
            wb = new HSSFWorkbook(in);
        } else if (fileName.endsWith(EXCEL_XLSX)) {
            wb = new XSSFWorkbook(in);
        }
        return wb;
    }

    /**
     * @描述: 通过文件流来获取workbook
     * @入参:
     * @出参:
     * @作者: 余家鑫
     * @日期: 2021/4/14 上午10:20
     */
    public static Workbook getWorkbook(InputStream excelInputStream, String excelSuffix) throws IOException {
        Workbook wb = null;
        if (excelSuffix.equals(EXCEL_XLS)) {
            wb = new HSSFWorkbook(excelInputStream);
        } else if (excelSuffix.equals(EXCEL_XLSX)) {
            wb = new XSSFWorkbook(excelInputStream);
        }
        return wb;
    }

    /**
     * @描述: 组装workbook
     * @入参:
     * @出参:
     * @作者: 余家鑫
     * @日期: 2021/4/26 上午11:05
     */
    public static Workbook buildWorkbook(List<?> list, String title, String sheetName, Class<?> pojoClass) {
        return defaultBuildWorkbook(list, pojoClass, new ExportParams(title, sheetName));
    }

    private static Workbook defaultBuildWorkbook(List<?> list, Class<?> pojoClass, ExportParams exportParams) {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        return workbook;
    }
    /**
     * @描述: 获取单元格的数据,并转化为字符串
     * @入参:
     * @出参:
     * @作者: 余家鑫
     * @日期: 2021/4/14 上午10:20
     */
    public static String getCellStringValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        CellType type = cell.getCellTypeEnum();
        String cellValue;
        switch (type) {
            case BLANK:
                cellValue = "";
                break;
            case _NONE:
                cellValue = "";
                break;
            case ERROR:
                cellValue = "";
                break;
            case BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case NUMERIC:
                cellValue = NumberToTextConverter.toText(cell.getNumericCellValue());
                break;
            case STRING:
                cellValue = cell.getStringCellValue();
                break;
            case FORMULA:
                cellValue = cell.getCellFormula();
                break;
            default:
                cellValue = "default";
                break;
        }
        return cellValue.trim();
    }

    /**
     * @描述: 获取单元格的数据,并转化为字符串
     * @入参:
     * @出参:
     * @作者: 余家鑫
     * @日期: 2021/4/14 上午10:20
     */
    public static String getCellStringValue(Cell cell, FormulaEvaluator evaluator) {
        if (cell == null) {
            return "";
        }

        CellType type = cell.getCellTypeEnum();
        String cellValue;
        switch (type) {
            case BLANK:
                cellValue = "";
                break;
            case _NONE:
                cellValue = "";
                break;
            case ERROR:
                cellValue = "";
                break;
            case BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case NUMERIC:
                cellValue = NumberToTextConverter.toText(cell.getNumericCellValue());
                break;
            case STRING:
                cellValue = cell.getStringCellValue();
                break;
            case FORMULA:
                throw new RuntimeException("不支持使用公式计算, 请把公式计算的列转化为数值");
                //cellValue = getCellValue(evaluator.evaluate(cell));
                //cellValue = cell.getCellFormula();
                //break;
            default:
                cellValue = "default";
                break;
        }
        return cellValue.trim();
    }

    /**
     * @描述: 从sheet中获取某行某列的数据
     * @入参: sheet 工作表
     * @入参: rowNum 行数, 从0 开始
     * @入参: cellNum 列数, 从0 开始
     * @出参:
     * @作者: 余家鑫
     * @日期: 2021/4/14 上午10:24
     */
    public static String getCellStringValue(Sheet sheet, int rowNum, int cellNum){
        log.info("--- getCellStringValue --- rowNum = {}, cellNum = {}", rowNum, cellNum);
        if(Objects.isNull(sheet)){
            return "";
        }
        Row row = sheet.getRow(rowNum);
        if(Objects.isNull(row)){
            return "";
        }
        Cell cell = row.getCell(cellNum);
        String value = getCellStringValue(cell);
        return value;
    }

    public static String getCellStringValue(Sheet sheet, int rowNum, int cellNum, FormulaEvaluator evaluator){
        log.info("--- getCellStringValue --- rowNum = {}, cellNum = {}", rowNum, cellNum);
        if(Objects.isNull(sheet)){
            return "";
        }
        Row row = sheet.getRow(rowNum);
        if(Objects.isNull(row)){
            return "";
        }
        Cell cell = row.getCell(cellNum);
        String value = getCellStringValue(cell, evaluator);
        return value;
    }

    /**
     * @描述: 导出workbook
     * @入参:
     * @出参:
     * @作者: 余家鑫
     * @日期: 2021/4/26 上午10:33
     */
    public static void exportWorkBook(String fileName, HttpServletResponse response, Workbook workbook) {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            e.printStackTrace();
        } finally {

        }
    }

    /**
     * @描述: 合并某一列的单元格
     * @入参:
     * @出参:
     * @作者: 余家鑫
     * @日期: 2021/5/20 下午5:12
     */
    public static void margeColumnCell(Sheet sheet, int column, int lineStart, int lineEnd, String content, CellStyle cellStyle){
        if(lineStart > lineEnd){
            System.out.println("结束行必须大于开始行!!");
            return;
        }
        Row row = sheet.getRow(lineStart);
        if (row == null) {
            row = sheet.createRow(lineStart);
        }
        row.createCell(column).setCellValue(content);
        row.getCell(column).setCellStyle(cellStyle);

        if(lineStart == lineEnd){
            System.out.println("合并行数小于2, 无法进行合并");
            return;
        }
        CellRangeAddress cellRangeAddress = new CellRangeAddress(lineStart/* lirst row (0-based) */
                , lineEnd /* last row (0-based) */
                , column // first column (0-based)
                , column // last column (0-based)
        );
        sheet.addMergedRegion(cellRangeAddress);
    }

    /**
     * @描述: 合并某一行
     * @入参:
     * @出参:
     * @作者: 余家鑫
     * @日期: 2021/6/29 上午11:08
     */
    public static void margeLineCell(Sheet sheet, int line, int cellStart, int cellEnd, String content,
                                     CellStyle cellStyle) {
        Row row = sheet.getRow(line);
        if (row == null) {
            row = sheet.createRow(line);
        }
        row.getRowNum();
        row.createCell(cellStart).setCellValue(content);
        row.getCell(cellStart).setCellStyle(cellStyle);
        CellRangeAddress cellRangeAddress = new CellRangeAddress(line/* lirst row (0-based) */
                , line /* last row (0-based) */
                , cellStart // first column (0-based)
                , cellEnd // last column (0-based)
        );
        sheet.addMergedRegion(cellRangeAddress);
        setBorderStyle(cellStyle.getBorderTopEnum(), cellRangeAddress, sheet);
    }

    /**
     * @描述: 设置边框类型
     * @入参:
     * @出参:
     * @作者: 余家鑫
     * @日期: 2021/6/29 上午11:08
     */
    public static void setBorderStyle(BorderStyle border, CellRangeAddress region, Sheet sheet) {
        RegionUtil.setBorderBottom(border, region, sheet); // 下边框
        RegionUtil.setBorderLeft(border, region, sheet); // 左边框
        RegionUtil.setBorderRight(border, region, sheet); // 右边框
        RegionUtil.setBorderTop(border, region, sheet); // 上边框
    }

    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response) {
        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setCreateHeadRows(isCreateHeader);
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }

    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) {
        if (list.size() > BIG_EXCEL_SIZE) {
            thousandsBigExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
        } else {
            defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
        }
    }

    static void thousandsBigExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams params) {
        Workbook workbook = null;
        int size = list.size();
        int page = size / BIG_EXCEL_SIZE + (size % BIG_EXCEL_SIZE > 0 ? 1 : 0);

        for (int j = 0; j < page; ++j) {
            //int toIndex = false;
            int toIndex;
            if (j == page - 1) {
                toIndex = size;
            } else {
                toIndex = BIG_EXCEL_SIZE * (j + 1);
            }

            List<?> dataList = list.subList(BIG_EXCEL_SIZE * j, toIndex);
            workbook = ExcelExportUtil.exportBigExcel(params, pojoClass, dataList);
        }

        ExcelExportUtil.closeExportBigExcel();
        if (workbook != null) {
            downLoadExcel(fileName, response, workbook);
        }

    }

    public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
        defaultExport(list, fileName, response);
    }

    private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
        Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
        if (workbook != null) {
            downLoadExcel(fileName, response, workbook);
        }
    }

    public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
        if (StringUtils.isBlank(filePath)) {
            return null;
        } else {
            ImportParams params = new ImportParams();
            params.setTitleRows(titleRows);
            params.setHeadRows(headerRows);
            List list = null;
            try {
                list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
            } catch (NoSuchElementException var7) {
                var7.printStackTrace();
            } catch (Exception var8) {
                var8.printStackTrace();
            }

            return list;
        }
    }

    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
        if (file == null) {
            return null;
        } else {
            ImportParams params = new ImportParams();
            params.setTitleRows(titleRows);
            params.setHeadRows(headerRows);
            List list = null;

            try {
                list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
            } catch (NoSuchElementException var7) {
                var7.printStackTrace();
            } catch (Exception var8) {
                var8.printStackTrace();
            }
            return list;
        }
    }

    private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (IOException var4) {
            var4.printStackTrace();
        }
    }

    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        if (workbook != null) {
            downLoadExcel(fileName, response, workbook);
        }
    }

    /**
     * @描述：导出单个工作表Excel，表头可以为合并单元格
     * @参数: response 输出流
     * @参数: fileName 导出的文件名
     * @参数: sheetName 导出的工作表名
     * @参数: titleConfigs 表格头配置，例如[["0","2","0","2","第一个合并单元格"], ["3","5","0","2","第二个合并单元格","r"]]，
     * 表示第一个单元格合并（A1,A2,A3,B1,B2,B3,C1,C2,C3）9个单元格，合并后默认居中
     * 第二个单元格合并（D1,D2,D3,E1,E2,E3,F1,F2,F3）9个单元格，合并后文本居右。
     * ["0","2","0","2","第一个合并单元格","r"]对应[起始列, 终止列,起始行,终止行,单元格文本,文本对齐方式]，也可以是[起始列, 终止列,起始行,终止行,单元格文本]
     * 对齐方式-（c：居中，l左对齐，r右对齐，默认c，不区分大小写）
     * @参数: dataList 数据列表
     * @参数: dataBeginIndex excel中开始填充数据的行下标，从0开始
     * @作者: 陈宏成
     * @时间: 2020-12-22
     */
    public static void exportExcel(HttpServletResponse response, String fileName, String sheetName
            , List<List<String>> titleConfigs, List<Object[]> dataList, int dataBeginIndex) {
        exportExcel(response, fileName, sheetName, titleConfigs, dataList, dataBeginIndex, new LinkedList<>());
    }

    /**
     * @描述：导出单个工作表Excel，表头可以为合并单元格
     * @参数: response 输出流
     * @参数: fileName 导出的文件名
     * @参数: sheetName 导出的工作表名
     * @参数: titleConfigs 表格头配置，例如[["0","2","0","2","第一个合并单元格"], ["3","5","0","2","第二个合并单元格","r"]]，
     * 表示第一个单元格合并（A1,A2,A3,B1,B2,B3,C1,C2,C3）9个单元格，合并后默认居中
     * 第二个单元格合并（D1,D2,D3,E1,E2,E3,F1,F2,F3）9个单元格，合并后文本居右。
     * ["0","2","0","2","第一个合并单元格","r"]对应[起始列, 终止列,起始行,终止行,单元格文本,文本对齐方式]，也可以是[起始列, 终止列,起始行,终止行,单元格文本]
     * 对齐方式-（c：居中，l左对齐，r右对齐，默认c，不区分大小写）
     * @参数: dataList 数据列表
     * @参数: dataBeginIndex excel中开始填充数据的行下标，从0开始
     * @参数: colorCellList 单元格填充颜色 例如[["0","2","0","2","#552299"], ["3","5","0","2","#00ffff"]]。对应[起始列, 终止列,起始行, 终止行, 颜色-必须是【#00ffff】这种格式]
     * @作者: 陈宏成
     * @时间: 2020-12-22
     */
    public static void exportExcel(HttpServletResponse response, String fileName, String sheetName
            , List<List<String>> titleConfigs, List<Object[]> dataList, int dataBeginIndex, List<String[]> colorCellList) {
        exportExcel(response, fileName, sheetName, titleConfigs, dataList, dataBeginIndex, colorCellList, new LinkedList<>());
    }

    /**
     * @描述：导出单个工作表Excel，表头可以为合并单元格
     * @参数: response 输出流
     * @参数: fileName 导出的文件名
     * @参数: sheetName 导出的工作表名
     * @参数: titleConfigs 表格头配置，例如[["0","2","0","2","第一个合并单元格"], ["3","5","0","2","第二个合并单元格","r"]]，
     * 表示第一个单元格合并（A1,A2,A3,B1,B2,B3,C1,C2,C3）9个单元格，合并后默认居中
     * 第二个单元格合并（D1,D2,D3,E1,E2,E3,F1,F2,F3）9个单元格，合并后文本居右。
     * ["0","2","0","2","第一个合并单元格","r"]对应[起始列, 终止列,起始行,终止行,单元格文本,文本对齐方式]，也可以是[起始列, 终止列,起始行,终止行,单元格文本]
     * 对齐方式-（c：居中，l左对齐，r右对齐，默认c，不区分大小写）
     * @参数: dataList 数据列表
     * @参数: dataBeginIndex excel中开始填充数据的行下标，从0开始
     * @参数: colorCellList 单元格填充颜色 例如[["0","2","0","2","#552299"], ["3","5","0","2","#00ffff"]]。对应[起始列, 终止列,起始行, 终止行, 颜色-必须是【#00ffff】这种格式]
     * @参数：alignCellList 单元格对齐列表，例如[["1","6","1","1","c"],["1","1","2","2","r"],["1","1","3","3","l"]]。
     * 对应[起始列, 终止列,起始行, 终止行, 对齐方式-（c：居中，l左对齐，r右对齐，默认l）这种格式]，对齐方式不区分大小写，
     * 可以对一排单元格进行设置，例如["1","6","1","1","c"]，将设置第二行第二列至第二行第七列中6个单元格统一设置为居中
     * @作者: 陈宏成
     * @时间: 2020-12-22
     */
    public static void exportExcel(HttpServletResponse response, String fileName, String sheetName
            , List<List<String>> titleConfigs, List<Object[]> dataList, int dataBeginIndex, List<String[]> colorCellList, List<String[]> alignCellList) {
        // 创建工作簿对象
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 创建工作表对象
        HSSFSheet sheet = workbook.createSheet();
        short height = 18;
        sheet.setDefaultRowHeightInPoints(height);
        workbook.setSheetName(0, sheetName);
        // 设置表格头，合并单元格
        for (List<String> titleConfig : titleConfigs) {
            mergeCell(sheet, Integer.valueOf(titleConfig.get(0)), Integer.valueOf(titleConfig.get(1))
                    , Integer.valueOf(titleConfig.get(2)), Integer.valueOf(titleConfig.get(3)), titleConfig.get(4), null,
                    titleConfig.size() <= 5 ? null : titleConfig.get(5));
        }
        // 创建数据行
        HSSFCellStyle hssfCellStyle = workbook.createCellStyle();
        hssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        for (Object[] datas : dataList) {
            HSSFRow newRow = sheet.createRow(dataBeginIndex++);
            for (int j = 0; j < datas.length; j++) {
                newRow.createCell(j).setCellValue(datas[j] == null ? "" : datas[j].toString());
                newRow.getCell(j).setCellStyle(hssfCellStyle);
            }
        }
        // 单元格颜色
        if (colorCellList != null && !colorCellList.isEmpty()) {
            // 单元格填充颜色
            short tIndex = IndexedColors.BLUE.index;
            // 取一个自定义调色板
            HSSFPalette palette = workbook.getCustomPalette();
            Map<String, Short> colorMap = new HashMap<>();
            for (String[] colorCell : colorCellList) {
                short colorIndex = 0;
                String colorHex = colorCell[4];
                if (colorMap.get(colorHex) == null) {
                    colorIndex = tIndex;

                    tIndex++;
                } else {
                    colorIndex = colorMap.get(colorHex);
                }
                int color1 = Integer.parseInt(colorHex.substring(1, 3), 16);
                int color2 = Integer.parseInt(colorHex.substring(3, 5), 16);
                int color3 = Integer.parseInt(colorHex.substring(5, 7), 16);

                palette.setColorAtIndex(colorIndex, (byte) color1, (byte) color2, (byte) color3);
                HSSFFont font = workbook.createFont();
                // 字体颜色
                font.setColor(colorIndex);
                colorMap.put(colorHex, colorIndex);
                // 定义一个单元格样式
                HSSFCellStyle style = workbook.createCellStyle();
                style.setFont(font);
                // 设置单元格
                int cellRowStart = Integer.valueOf(colorCell[2]);
                int cellRowEnd = Integer.valueOf(colorCell[3]);
                int cellColStart = Integer.valueOf(colorCell[0]);
                int cellColEnd = Integer.valueOf(colorCell[1]);
                // 先遍历行
                for (int i = cellRowStart; i <= cellRowEnd; i++) {
                    // 在遍历列
                    for (int j = cellColStart; j <= cellColEnd; j++) {
                        sheet.getRow(i).getCell(j).setCellStyle(style);
                    }
                }
            }
        }
        // 单元格对齐方式
        if (alignCellList != null && !alignCellList.isEmpty()) {
            for (String[] alignCell : alignCellList) {
                // 设置单元格
                int cellRowStart = Integer.valueOf(alignCell[2]);
                int cellRowEnd = Integer.valueOf(alignCell[3]);
                int cellColStart = Integer.valueOf(alignCell[0]);
                int cellColEnd = Integer.valueOf(alignCell[1]);
                // 定义一个单元格样式
                HSSFCellStyle style = workbook.createCellStyle();
                if ("c".equalsIgnoreCase(alignCell[4])) {
                    style.setAlignment(HorizontalAlignment.CENTER);
                } else if ("r".equalsIgnoreCase(alignCell[4])) {
                    style.setAlignment(HorizontalAlignment.RIGHT);
                } else {
                    style.setAlignment(HorizontalAlignment.LEFT);
                }
                // 先遍历行
                for (int i = cellRowStart; i <= cellRowEnd; i++) {
                    // 在遍历列
                    for (int j = cellColStart; j <= cellColEnd; j++) {
                        sheet.getRow(i).getCell(j).setCellStyle(style);
                    }
                }
            }
        }
        //设置自动列宽
        if (!dataList.isEmpty()) {
            for (int i = 0; i < dataList.get(0).length; i++) {
                sheet.autoSizeColumn(i);
                sheet.autoSizeColumn(i, true);
                sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 15 / 10);
            }
        }

        // 导出
        try {
            response.reset();
            response.setContentType("application/octet-stream");
            response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("UTF-8"), "ISO-8859-1") + ".xls");
//            response.setHeader("Access-Control-Allow-Origin", "*");
            response.setHeader("Access-Control-Allow-Methods", "GET,POST");
            OutputStream outputStream = response.getOutputStream();
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 合并单元格
     * @入参： sheet
     * @入参： startCol 起始列，下标从0开始
     * @入参： endCol 终止列
     * @入参： startRow 起始行，行下标从0开始
     * @入参： endRow 终止行
     * @入参： text 合并后的单元格文本
     * @入参： richTextString 富文本，如果不为空，单元格就填充richTextString。如果为空，单元格就填充text
     * @入参： align 文本对齐方式（c-居中，l-居左，r-居右），大小写不区分，默认居中
     * @作者： 陈宏成
     * @日期： 2020-12-22
     */
    @SuppressWarnings("unused")
    private static void mergeCell(Sheet sheet, int startCol, int endCol, int startRow, int endRow, String text,RichTextString richTextString, String align) {
        boolean isMerged = false;
        if (startRow == endRow && startCol == endCol) {
            // 如果只是一个单元格，没必要合并
            isMerged = false;
        } else {
            // 创建合并单元格
            CellRangeAddress cra = new CellRangeAddress(startRow, endRow, startCol, endCol);
            sheet.addMergedRegion(cra);
            isMerged = true;
        }
        // 设置单元格文本
        Row row = sheet.getRow(startRow) == null ? sheet.createRow(startRow) : sheet.getRow(startRow);
        Cell cell = row.getCell(startCol) == null ? row.createCell(startCol) : row.getCell(startCol);
        //合并的单元格样式
        CellStyle style = sheet.getWorkbook().createCellStyle();
        //设置居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        if (StringUtils.isNotBlank(align)) {
            if ("l".equalsIgnoreCase(align)) {
                style.setAlignment(HorizontalAlignment.LEFT);
            } else if ("r".equalsIgnoreCase(align)) {
                style.setAlignment(HorizontalAlignment.RIGHT);
            } else {
                style.setAlignment(HorizontalAlignment.CENTER);
            }
        } else {
            style.setAlignment(HorizontalAlignment.CENTER);
        }
        cell.setCellStyle(style);
        // 填单元格内容
        if (Objects.nonNull(richTextString)){
            cell.setCellValue(richTextString);
        }else{
            cell.setCellValue(text);
        }
    }

    /**
     * @描述：根据上传MultipartFile对象获取excel数据集
     * @作者：彭辉
     * @时间：2019/9/1 9:44
     */
    public static List<List<String>> getExcelData(MultipartFile file) {
        try {
            String fileName = file.getOriginalFilename();
            Workbook wb = null;
            InputStream is = file.getInputStream();
            // 根据文件格式(2003或者2007)来初始化
            if (fileName.endsWith(".xlsx")) {
                wb = new XSSFWorkbook(is);
            } else {
                wb = new HSSFWorkbook(is);
            }
            // 默认读取第一个表单
            Sheet sheet = wb.getSheetAt(0);
            // Sheet解析成List数据
            List<List<String>> dataList = getSheetData(sheet);
            return dataList;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * @描述：Sheet解析成List数据
     * @作者：彭辉
     * @时间：2019/9/1 9:43
     */
    private static List<List<String>> getSheetData(Sheet sheet) {
        List<List<String>> list = new ArrayList<>();
        int rowCount = sheet.getLastRowNum();
        // 格式化日期
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        for (int i = 0; i < rowCount + 1; i++) {
            Row row = sheet.getRow(i);
            if (null != row) {
                List<String> temp = new ArrayList<>();
                for (int j = 0; j < sheet.getRow(0).getPhysicalNumberOfCells(); j++) {
                    /*Cell cellValue = row.getCell(j);
                    if (cellValue == null) {
                        temp.add("");
                    } else if (cellValue.getCellType() == CellType.NUMERIC && HSSFDateUtil.isCellDateFormatted(cellValue)) {
                        temp.add(sdf.format(cellValue.getDateCellValue()));
                    } else {
                        temp.add(cellValue.toString());
                    }*/
                    temp.add(getCellStringValue(row.getCell(j)));
                }
                if (temp.get(0) == "" && temp.get(1) == "") {
                    break;
                }
                list.add(temp);
            }
        }
        return list;
    }

    /**
     * 导出excel，可以有多个sheet工作表
     * @入参： outputStream 输出流，用于接收excel    <br/>
     *
     * @入参： sheetNames （不能为空）sheet的名字    <br/>
     *
     * @入参： sheetDataMap 数据集，key为sheet的名字，value为sheet的数据列表。    <br/>
     *          单元格内容有这种格式：###$颜色$内容文本$$###，例如：###$#9922ff$这里是单元格的值$$###，则这段文本会被加上指定颜色。    <br/>
     *
     * @入参： dataBeginIndexMap sheetDataMap从excel的哪一行开始填充，从0开始，key为sheet的名字，与sheetDataMap的key对应    <br/>
     *
     * @入参： mergeConfigs 合并单元格配置，key为sheet的名字，与sheetDataMap的key对应。value为合并单元格配置。    <br/>
     * 例如[["0","2","0","2","第一个合并单元格"], ["3","5","0","2","第二个合并单元格","l"]]    <br/>
     * ，表示第一个单元格合并（A1,A2,A3,B1,B2,B3,C1,C2,C3）9个单元格，默认内容居中。第二个单元格合并（D1,D2,D3,E1,E2,E3,F1,F2,F3）9个单元格，内容居左。    <br/>
     * ["0","2","0","2","第一个合并单元格","内容对齐方式"]对应[起始列, 终止列,起始行, 终止行, 单元格内容, 内容对齐方式（c-居中，l-居左，r-居右）]    <br/>
     * 内容对齐方式可以不填，只有5个元素，key为sheet的名字，与sheetDataMap的key对应。value为合并单元格样式配置。    <br/>
     *
     * @入参： styleConfigs 单元格样式配置，key为sheet的名字，与sheetDataMap的key对应。value为单元格样式配置。例如[["起始列", "终止列","起始行", "终止行","对齐方式","加粗斜体"]]    <br/>
     *                     如果不需要对齐，可以填空字符串，或其他值        <br/>
     *                     对齐方式：r - 右对齐；l - 左对齐；c - 居中；其他 - 默认，不额外设置    <br/>
     *                     加粗斜体：b - 加粗；i - 斜体；bi/ib - 斜体加粗；其他 - 默认，不额外设置    <br/>
     *
     * @入参： isFlushAndCloseStream 是否刷新和关闭输出流，如果是false，需要外层自己关闭    <br/>
     */
    public static void exportExcelXLSX(OutputStream outputStream,List<String> sheetNames, Map<String, List<Object[]>> sheetDataMap, Map<String, Integer> dataBeginIndexMap
            , Map<String, List<String[]>> mergeConfigs, Map<String, List<String[]>> styleConfigs, boolean isFlushAndCloseStream) throws IOException {
        // 创建工作簿对象
        XSSFWorkbook workbook = new XSSFWorkbook();
        if (CollectionUtils.isEmpty(sheetNames)){
            throw new RuntimeException("sheetNames：excel工作簿名称列表不能为空");
        }
        int sheetIndex = 0;
        for(String sheetName : sheetNames) {
            // sheet 数据
            List<Object[]> sheetData = Objects.isNull(sheetDataMap)? new LinkedList<>(): sheetDataMap.getOrDefault(sheetName, new LinkedList<>());
            // sheet 数据列表开始填充的下标
            int dataBeginIndex = Objects.isNull(dataBeginIndexMap)? 0: dataBeginIndexMap.getOrDefault(sheetName, 0);
            // 合并单元格配置
            List<String[]> mergeCellList = Objects.isNull(mergeConfigs)? new LinkedList<>(): mergeConfigs.getOrDefault(sheetName, new LinkedList<>());
            // 单元格样式配置
            List<String[]> styleConfigsList = Objects.isNull(styleConfigs)? new LinkedList<>(): styleConfigs.getOrDefault(sheetName, new LinkedList<>());
            // 创建工作表对象
            XSSFSheet sheet = workbook.createSheet();
            short height = 18;
            sheet.setDefaultRowHeightInPoints(height);
            workbook.setSheetName(sheetIndex++, sheetName);
            // 创建数据行
            XSSFCellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            // 放数据
            for (Object[] datas : sheetData) {
                XSSFRow newRow = sheet.createRow(dataBeginIndex++);
                for (int j = 0; j < datas.length; j++) {
                    String cellValue = datas[j] == null ? "" : datas[j].toString();

                    XSSFRichTextString xssfRichTextString = setColor(cellValue, workbook);

                    newRow.createCell(j).setCellValue(xssfRichTextString);
                    newRow.getCell(j).setCellStyle(cellStyle);
                }
            }
            //设置数据列表自动列宽
            if (!CollectionUtils.isEmpty(sheetData)) {
                for (int i = 0, size = sheetData.size(); i < size; i++) {
                    sheet.autoSizeColumn(i);
                    sheet.autoSizeColumn(i, true);
                    sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 15 / 10);
                }
            }
            // 设置表格头，合并单元格
            for (String[] mergeCell : mergeCellList) {
                String cellValue = mergeCell[4] == null ? "" : mergeCell[4].toString();

                XSSFRichTextString xssfRichTextString = setColor(cellValue, workbook);

                mergeCell(sheet, Integer.valueOf(mergeCell[0]), Integer.valueOf(mergeCell[1])
                        , Integer.valueOf(mergeCell[2]), Integer.valueOf(mergeCell[3]), cellValue, xssfRichTextString, mergeCell.length <= 5 ? null : mergeCell[5]);
            }
            // 设置单元格样式
            for(String[] config : styleConfigsList) {
                // 设置单元格
                int cellRowStart = Integer.valueOf(config[2]);
                int cellRowEnd = Integer.valueOf(config[3]);
                int cellColStart = Integer.valueOf(config[0]);
                int cellColEnd = Integer.valueOf(config[1]);

                String alignStyle = config.length >= 5 ? config[4]: "";
                String textFontStyle = config.length >= 6 ? config[5]: "";
                // 对齐方式
                HorizontalAlignment alignment = null;
                // 是否斜体
                Boolean italic = null;
                // 是否加粗
                Boolean bold = null;

                // 对齐方式
                if(StringUtils.isNotBlank(alignStyle) ) {
                    if ("c".equalsIgnoreCase(alignStyle)) {
                        alignment = HorizontalAlignment.CENTER;
                    } else if ("r".equalsIgnoreCase(alignStyle)) {
                        alignment = HorizontalAlignment.RIGHT;
                    } else if ("l".equalsIgnoreCase(alignStyle)) {
                        alignment = HorizontalAlignment.LEFT;
                    }
                }

                // 设置加粗斜体
                if(StringUtils.isNotBlank(textFontStyle) ){
                    if (textFontStyle.trim().equalsIgnoreCase("b")){
                        bold = true;
                    }else if (textFontStyle.trim().equalsIgnoreCase("i")){
                        italic = true;
                    }else if (textFontStyle.trim().equalsIgnoreCase("ib") || textFontStyle.trim().equalsIgnoreCase("bi")){
                        bold = true;
                        italic = true;
                    }
                }

                // 先遍历行
                for (int i = cellRowStart; i <= cellRowEnd; i++) {
                    // 在遍历列
                    for (int j = cellColStart; j <= cellColEnd; j++) {
                        XSSFCell cell = sheet.getRow(i).getCell(j);
                        if (Objects.isNull(cell)){
                            continue;
                        }
                        XSSFCellStyle oldStyle = cell.getCellStyle();
                        XSSFFont oldFont = oldStyle.getFont();
                        // 定义一个单元格样式
                        XSSFCellStyle newStyle = workbook.createCellStyle();
                        // 克隆样式
                        newStyle.cloneStyleFrom(oldStyle);

                        // 设置新样式
                        if (Objects.nonNull(alignment)){
                            newStyle.setAlignment(alignment);
                            cell.setCellStyle(newStyle);
                        }

                        if (Objects.nonNull(italic) || Objects.nonNull(bold)){
                            // 定义字体
                            XSSFFont newFont = sheet.getWorkbook().createFont();

                            copyPropertiesForFont(oldFont,newFont);
                            newStyle.setFont(newFont);
                            //XSSFFont newFont = newStyle.getFont();
                            if (Objects.nonNull(italic) ){
                                newFont.setItalic(italic);
                            }
                            if (Objects.nonNull(bold) ){
                                newFont.setBold(bold);
                            }
                            cell.setCellStyle(newStyle);
                        }
                    }
                }
            }
        }
        // 写Excel
        workbook.write(outputStream);
        // 关闭流
        if (isFlushAndCloseStream) {
            outputStream.flush();
            outputStream.close();
        }
    }


    /**
     * @描述：导出excel，可以有多个sheet工作表
     * @参数： outputStream 输出流，用于接收excel
     * @参数： sheetDataMap 数据集，key为sheet的名字，value为sheet的数据列表
     * @参数： dataBeginIndexMap 数据起始行下标，从0开始，key为sheet的名字，与sheetDataMap的key对应
     * @参数： mergeConfigs 合并单元格配置，key为sheet的名字，与sheetDataMap的key对应。value为合并单元格配置。
     *          例如[["0","2","0","2","第一个合并单元格"], ["3","5","0","2","第二个合并单元格","l"]]
     *          ，表示第一个单元格合并（A1,A2,A3,B1,B2,B3,C1,C2,C3）9个单元格，默认内容居中。第二个单元格合并（D1,D2,D3,E1,E2,E3,F1,F2,F3）9个单元格，内容居左。
     *          ["0","2","0","2","第一个合并单元格","内容对齐方式"]对应[起始列, 终止列,起始行, 终止行, 单元格内容, 内容对齐方式（c-居中，l-居左，r-居右）]
     *          内容对齐方式可以不填，只有5个元素
     * @参数： isFlushAndCloseStream 是否刷新和关闭输出流，如果是false，需要外层自己关闭
     */
    public static void exportExcelXLS(OutputStream outputStream, Map<String,List<Object[]>> sheetDataMap,Map<String,Integer> dataBeginIndexMap
            ,Map<String,List<String[]>> mergeConfigs, boolean isFlushAndCloseStream) throws IOException {
        // 创建工作簿对象
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 遍历创建每个工作表
        Set<Map.Entry<String, List<Object[]>>> entrySet = sheetDataMap.entrySet();
        int sheetIndex = 0;
        for(Map.Entry<String, List<Object[]>> sheetData : entrySet) {
            int dataBeginIndex = dataBeginIndexMap.getOrDefault(sheetData.getKey(),0);
            // 创建工作表对象
            HSSFSheet sheet = workbook.createSheet();
            short height = 18;
            sheet.setDefaultRowHeightInPoints(height);
            workbook.setSheetName(sheetIndex++, sheetData.getKey());
            // 创建数据行
            HSSFCellStyle hssfCellStyle = workbook.createCellStyle();
            hssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            // 放数据
            for (Object[] datas : sheetData.getValue()) {
                HSSFRow newRow = sheet.createRow(dataBeginIndex++ );
                for (int j = 0; j < datas.length; j++) {
                    newRow.createCell(j).setCellValue(datas[j] == null ? "" : datas[j].toString());
                    newRow.getCell(j).setCellStyle(hssfCellStyle);
                }
            }
            // 设置表格头，合并单元格
            List<String[]> mergeCellList = mergeConfigs.getOrDefault(sheetData.getKey(),new LinkedList<>());
            for (String[] mergeCell : mergeCellList) {
                mergeCell(sheet, Integer.valueOf(mergeCell[0]), Integer.valueOf(mergeCell[1])
                        , Integer.valueOf(mergeCell[2]), Integer.valueOf(mergeCell[3]),mergeCell[4], mergeCell.length <= 5? null: mergeCell[5]);
            }
            //设置自动列宽
            if (!sheetData.getValue().isEmpty()) {
                for (int i = 0,size = sheetData.getValue().size(); i < size; i++) {
                    sheet.autoSizeColumn(i);
                    sheet.autoSizeColumn(i, true);
                    sheet.setColumnWidth(i,sheet.getColumnWidth(i) * 15/10);
                }
            }
        }
        // 写Excel
        workbook.write(outputStream);
        // 关闭流
        if (isFlushAndCloseStream){
            outputStream.flush();
            outputStream.close();
        }
    }

    /**
     * 	@描述：合并单元格
     * 	@参数: sheet
     * 	@参数: startCol 起始列，下标从0开始
     * 	@参数: endCol 终止列
     * 	@参数: startRow 起始行，行下标从0开始
     * 	@参数: endRow 终止行
     * 	@参数: text 合并后的单元格文本
     * 	@参数: align 文本对齐方式（c-居中，l-居左，r-居右），大小写不区分，默认居中
     *  @作者: 陈宏成
     *  @时间: 2020-12-22
     * */
    @SuppressWarnings("unused")
    private static void mergeCell(HSSFSheet sheet,int startCol,int endCol,int startRow,int endRow,String text,String align) {
        boolean isMerged = false;
        if(startRow == endRow && startCol == endCol){
            // 如果只是一个单元格，没必要合并
            isMerged = false;
        }else{
            // 创建合并单元格
            CellRangeAddress cra = new CellRangeAddress(startRow, endRow, startCol, endCol);
            sheet.addMergedRegion(cra);
            isMerged = true;
        }
        // 设置单元格文本
        HSSFRow row = sheet.getRow(startRow) == null? sheet.createRow(startRow):sheet.getRow(startRow);
        HSSFCell cell = row.getCell(startCol) == null? row.createCell(startCol): row.getCell(startCol);
        if(isMerged){
            //合并的单元格样式
            HSSFCellStyle style = sheet.getWorkbook().createCellStyle();
            //设置居中
            style.setVerticalAlignment(VerticalAlignment.CENTER);
            if(StringUtils.isNotBlank(align) ){
                if("l".equalsIgnoreCase(align)){
                    style.setAlignment(HorizontalAlignment.LEFT);
                }else if ("r".equalsIgnoreCase(align)){
                    style.setAlignment(HorizontalAlignment.RIGHT);
                }else{
                    style.setAlignment(HorizontalAlignment.CENTER);
                }
            }else{
                style.setAlignment(HorizontalAlignment.CENTER);
            }
            cell.setCellStyle(style);
        }
        cell.setCellValue(text);
    }

    /**
     *  复制字体属性
     * @入参： oldFont
     * @入参： newFont
     */
    private static void copyPropertiesForFont(XSSFFont oldFont, XSSFFont newFont){
        newFont.setBold(oldFont.getBold());
        newFont.setCharSet(oldFont.getCharSet());

        newFont.setFontHeight(oldFont.getFontHeight());
        newFont.setFontHeightInPoints(oldFont.getFontHeightInPoints());
        newFont.setThemeColor(oldFont.getThemeColor());
        newFont.setFontName(oldFont.getFontName());
        newFont.setItalic(oldFont.getItalic());
        newFont.setStrikeout(oldFont.getStrikeout());
        newFont.setTypeOffset(oldFont.getTypeOffset());
        newFont.setUnderline(oldFont.getUnderline());
        newFont.setScheme(oldFont.getScheme());
        newFont.setFamily(oldFont.getFamily());

        newFont.setColor(oldFont.getColor());

        if (Objects.nonNull(oldFont.getXSSFColor())){
            newFont.setColor(oldFont.getXSSFColor());
        }
    }

    /**
     * 构建合并单元格，主要是为了构建合并单元格的配置
     * 合并数据项中的单元格。<br/>
     * 纵向合并是通过判断单元格的值是否相同，上下连续单元格的值相同，且左侧一列的单元格也是相同的，就合并。<br/>
     * 横向合并是通过判断null，如果单元格为null，就往左侧单元格合并。<br/>
     * @入参：  mergeIndexs 数据项中要合并的列的下标 ，{0,1,2,3}，注意，这里的下标是从0开始的
     * @入参： excelDataList 数据列表
     * @入参： dataRowStartIndex sheet中开始填充数据列表的行下标
     * @出参： 数据项中合并的单元格
     * @作者： 陈宏成
     * @日期： 2021/01/21
     **/
    public static List<List<String>> buildMergeCellsForExcel(int[] mergeIndexs, List<Object[]> excelDataList, int dataRowStartIndex){
        List<List<String>> mergeTableCellList = new LinkedList<>();
        int rowStart = 0, rowEnd = 0, colStart = 0, colEnd = 0;
        // 每列纵向合并
        for(int mergeIndex : mergeIndexs) {
            rowStart = dataRowStartIndex;
            rowEnd = dataRowStartIndex;
            colStart = mergeIndex;
            colEnd = mergeIndex;
            // 遍历所有行
            String title = null;            // 上一单元格的值
            String titleParent = null;      // 上一单元格左侧单元格的值
            for(int i = 0; i < excelDataList.size(); i++) {
                Object[] data = excelDataList.get(i);
                // 获取当前列的单元格值
                String tempTitle =  (String)data[mergeIndex];
                // 获取左边一列的单元格值
                String tempParentTitle =  mergeIndex > 0? String.valueOf(data[mergeIndex-1]) : null;
                if(title == null){
                    // 如果是第一行，直接赋值
                    title = tempTitle;
                    titleParent = tempParentTitle;
                } else if ((title.equals(tempTitle) && mergeIndex == mergeIndexs[0])  || (title.equals(tempTitle) && titleParent.equals(tempParentTitle) && mergeIndex > mergeIndexs[0])){
                    // 同时也判断一下和左侧单元格是否相等，主要是为了防止当前单元格一样，但是左侧单元格不同，从而导致交叉合并单元格。如果是第一列，则没有左侧单元格，所以不用比较。
                    // 例如下面，虽然“打游戏”是一样，但是由于前面不同，所以他们归属不同的分类，不能做纵向合并，
                    // |    爱好    |    打游戏    |
                    // |    职业    |    打游戏    |
                    rowEnd++;
                    // 如果是最后一条记录，直接添加一个合并单元格
                    if(i == excelDataList.size() - 1){
                        mergeTableCellList.add(Arrays.asList(String.valueOf(colStart),String.valueOf(colEnd),String.valueOf(rowStart),String.valueOf(rowEnd),tempTitle));
                    }
                }else{
                    // 不满足合并条件，即和下一个单元格不同
                    // 添加一个合并单元格
                    mergeTableCellList.add(Arrays.asList(String.valueOf(colStart),String.valueOf(colEnd),String.valueOf(rowStart),String.valueOf(rowEnd),title));
                    // 记录当前的文本内容
                    title = tempTitle;
                    titleParent = tempParentTitle;
                    // 重置四个下标
                    rowStart = i + dataRowStartIndex;
                    rowEnd = rowStart;
                }
            }
        }
        // 横向合并，如果指标名称为空，则则与左边单元格进行合并
        for(int i = 0; i < excelDataList.size(); i++) {
            rowStart = dataRowStartIndex + i;
            rowEnd = rowStart;
            colStart = mergeIndexs[mergeIndexs.length - 1];
            colEnd = colStart;
            Object[] data = excelDataList.get(i);
            // [上上上级单元格，上上级单元格，上级单元格，单元格] ，初始只有最右侧的单元格，依次往左扩展单元格数量
            for(int j = mergeIndexs.length - 1; j >= 0; j--) {
                String tempTitle = (String)data[mergeIndexs[j]];
                if (tempTitle == null){
                    colStart--;
                }else{
                    break;
                }
            }
            mergeTableCellList.add(Arrays.asList(String.valueOf(colStart),String.valueOf(colEnd),String.valueOf(rowStart),String.valueOf(rowEnd),String.valueOf(data[colStart])));
        }
        return mergeTableCellList;
    }

    /**
     * 和 buildMergeCellsForExcel 相比返回格式不同
     * @see ExcelUtil#buildMergeCellsForExcel(int[], List, int)
     * @return
     */
    public static List<String[]> buildMergeCellsForExcel2(int[] mergeIndexs, List<Object[]> excelDataList, int dataRowStartIndex){
        List<List<String>> lists = buildMergeCellsForExcel(mergeIndexs, excelDataList, dataRowStartIndex);
        List<String[]> collect = lists.stream().map(item -> {
            String[] arr = new String[item.size()];
            String[] strings = item.toArray(arr);
            return arr;
        }).collect(Collectors.toList());
        return collect;
    }

    /**
     * 设置单元格文本颜色，可以实现一个单元格内多种颜色，只针对2007版的xlsx，不兼容2003版的xls
     * @入参： cellValue 单元格文本，###$颜色$内容文本$$###，例如：###$#9922ff$这里是单元格的值$$###，则这段文本会被加上指定颜色。
     * @入参： workbook
     * @return
     * @作者： 陈宏成
     * @日期： 2022-02-18
     */
    private static XSSFRichTextString setColor(String cellValue, XSSFWorkbook workbook){
        Pattern colorTextPattern = Pattern.compile("###\\$(#[0-9a-fA-F]{6})\\$([\\s\\S]+?)\\$\\$###");
        Pattern colorPattern = Pattern.compile("###\\$(#[0-9a-fA-F]{6})\\$");
        if (!colorTextPattern.matcher(cellValue).find()) {
            return new XSSFRichTextString(cellValue);
        }
        // 真实文本
        String realText = colorPattern.matcher(cellValue).replaceAll("").replaceAll("\\$\\$###", "");
        XSSFRichTextString xssfRichTextString = new XSSFRichTextString(realText);
        // 判断颜色
        Matcher matcher = colorTextPattern.matcher(cellValue);

        int startIndex = 0, endIndex = 0;
        while (matcher.find( )) {
            String color = matcher.group(1);
            String text = matcher.group(2);

            XSSFFont font = (XSSFFont)workbook.createFont();
            DefaultIndexedColorMap defaultIndexedColorMap = new DefaultIndexedColorMap();
            XSSFColor xssfColor = new XSSFColor(new java.awt.Color(Integer.parseInt(color.substring(1), 16)),defaultIndexedColorMap);
            font.setColor(xssfColor);
            // 文本位置
            startIndex = realText.indexOf( text, startIndex);
            endIndex = startIndex + text.length();

            xssfRichTextString.applyFont(startIndex, endIndex, font);
        }
        return xssfRichTextString;
    }

    /**
     * * 合并多个工作簿
     *
     * @入参： workbookList     工作簿列表
     * @return
     * @Date: 2020/9/18 15:31
     */
    public static Workbook mergeWorkbook(List<XSSFWorkbook> workbookList) {
        XSSFWorkbook newExcelCreat = new XSSFWorkbook();
        // 遍历每个源excel文件，TmpList为源文件的名称集合
        for (XSSFWorkbook workbook : workbookList) {
            int length = workbook.getNumberOfSheets();
            if (length <= 1) {       //长度为1时
                XSSFSheet oldSheet = workbook.getSheetAt(0);
                XSSFSheet newSheet = newExcelCreat.createSheet(oldSheet.getSheetName());
                copySheet(newExcelCreat, oldSheet, newSheet);
            } else {
                for (int i = 0; i < length; i++) {// 遍历每个sheet
                    XSSFSheet oldSheet = workbook.getSheetAt(i);
                    XSSFSheet newSheet = newExcelCreat.createSheet(oldSheet.getSheetName());
                    copySheet(newExcelCreat, oldSheet, newSheet);
                }
            }

        }
        return newExcelCreat;
    }

    /**
     * 合并单元格
     *
     * @入参： fromSheet
     * @入参： toSheet
     */
    private static void mergeSheetAllRegion(XSSFSheet fromSheet, XSSFSheet toSheet) {
        int num = fromSheet.getNumMergedRegions();
        CellRangeAddress cellR = null;
        for (int i = 0; i < num; i++) {
            cellR = fromSheet.getMergedRegion(i);
            toSheet.addMergedRegion(cellR);
        }
    }

    /**
     * 复制单元格
     *
     * @入参： wb
     * @入参： fromCell
     * @入参： toCell
     */
    private static void copyCell(XSSFWorkbook wb, XSSFCell fromCell, XSSFCell toCell) {
        XSSFCellStyle newstyle = wb.createCellStyle();
        // 复制单元格样式
        newstyle.cloneStyleFrom(fromCell.getCellStyle());
        // 样式
        toCell.setCellStyle(newstyle);
        if (fromCell.getCellComment() != null) {
            toCell.setCellComment(fromCell.getCellComment());
        }
        // 不同数据类型处理
        CellType fromCellType = fromCell.getCellType();
        toCell.setCellType(fromCellType);
        if (fromCellType == CellType.NUMERIC) {
            if (DateUtil.isCellDateFormatted(fromCell)) {
                toCell.setCellValue(fromCell.getDateCellValue());
            } else {
                toCell.setCellValue(fromCell.getNumericCellValue());
            }
        } else if (fromCellType == CellType.STRING) {
            toCell.setCellValue(fromCell.getRichStringCellValue());
        } else if (fromCellType == CellType.BLANK) {
            // nothing21
        } else if (fromCellType == CellType.BOOLEAN) {
            toCell.setCellValue(fromCell.getBooleanCellValue());
        } else if (fromCellType == CellType.ERROR) {
            toCell.setCellErrorValue(fromCell.getErrorCellValue());
        } else if (fromCellType == CellType.FORMULA) {
            toCell.setCellFormula(fromCell.getCellFormula());
        } else {
            // nothing29
        }
    }

    /**
     * 行复制功能
     *
     * @入参： wb
     * @入参： oldRow
     * @入参： toRow
     */
    private static void copyRow(XSSFWorkbook wb, XSSFRow oldRow, XSSFRow toRow) {
        toRow.setHeight(oldRow.getHeight());
        for (Iterator cellIt = oldRow.cellIterator(); cellIt.hasNext(); ) {
            XSSFCell tmpCell = (XSSFCell) cellIt.next();
            XSSFCell newCell = toRow.createCell(tmpCell.getColumnIndex());
            copyCell(wb, tmpCell, newCell);
        }
    }

    /**
     * Sheet复制
     *
     * @入参： wb
     * @入参： fromSheet
     * @入参： toSheet
     */
    private static void copySheet(XSSFWorkbook wb, XSSFSheet fromSheet, XSSFSheet toSheet) {
        mergeSheetAllRegion(fromSheet, toSheet);
        // 设置列宽
        int length = fromSheet.getRow(fromSheet.getFirstRowNum()).getLastCellNum();
        for (int i = 0; i <= length; i++) {
            toSheet.setColumnWidth(i, fromSheet.getColumnWidth(i));
        }
        for (Iterator rowIt = fromSheet.rowIterator(); rowIt.hasNext(); ) {
            XSSFRow oldRow = (XSSFRow) rowIt.next();
            XSSFRow newRow = toSheet.createRow(oldRow.getRowNum());
            copyRow(wb, oldRow, newRow);
        }
    }
}

